﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using HISModel;
using Newtonsoft.Json;
namespace HISDAL
{
    public class log_indal  
    {
        SqlConnection con = new SqlConnection("Data Source=169.254.82.63;Initial Catalog=HospitalSystem;User ID=sa;password=asd_123");
        /// <summary>
        /// 管理员登录方法
        /// </summary>
        /// <param name="T_Admin"></param>
        /// <returns></returns>
        public DataTable T_Admin()
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM T_Admin ",con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable de = new DataTable();
            da.Fill(de);
            con.Dispose();
            return de;
        }
        /// <summary>
        /// 普通登录方法
        /// </summary>
        /// <param name="T_Admin"></param>
        /// <returns></returns>
        public DataTable T_User()
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM T_User ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable de = new DataTable();
            da.Fill(de);
            con.Dispose();
            return de;
        }
        /// <summary>
        /// 查询角色数据
        /// </summary>
        /// <returns></returns>
        public List<T_Role> getdatatble()
        {
            string sql = "select * from T_Role";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable de = new DataTable();
            da.Fill(de);
            var result = from s in de.AsEnumerable()
                         select new T_Role
                         {
                             Role_ID = s.Field<int>("Role_ID"),
                             Role_Name = s.Field<string>("Role_Name")
                         };
            return result.ToList();
        }
        /// <summary>
        /// 管理员添加方法
        /// </summary>
        /// <param name="T_Admin"></param>
        /// <returns></returns>
        public int appendAdmin(string Admin_Name, string Admin_Pwd, int Role_ID)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO T_Admin VALUES('" + Admin_Name + "','" + Admin_Pwd + "','" + Role_ID + "')", con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return result;
        }
        /// <summary>
        /// 普通添加方法
        /// </summary>
        /// <param name="T_Admin"></param>
        /// <returns></returns>
        public int append(string U_Name, string U_Pwd, string U_Sex, int U_Age, string U_Phone, string U_IdCard)
        {
            con.Open();
            SqlCommand cmd  = new SqlCommand("INSERT INTO T_User VALUES('"+ U_Name+ "','"+ U_Pwd+ "','"+ U_Sex+ "','"+ U_Age+ "','"+ U_Phone+ "','"+ U_IdCard+ "')", con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return result;
        }
        /// <summary>
        /// 权限数据
        /// </summary>
        /// <returns></returns>
       public List<T_Control> T_Control(int id)
        {
            SqlDataAdapter da = new SqlDataAdapter(@"SELECT * FROM Rolepermissionassociation a INNER JOIN T_Role b ON a.RoleID=b.Role_ID
INNER JOIN T_Control c ON a.T_Control_ID = c.Control_ID WHERE a.T_Role_ID = " + id,con);
            DataTable de = new DataTable();
            da.Fill(de);
            var s = JsonConvert.SerializeObject(de);
            var list = JsonConvert.DeserializeObject<List<T_Control>>(s);
            return list;
        }
        /// <summary>
        /// 添加、修改医院信息
        /// </summary>
        /// <param name="Hosp"></param>
        /// <returns></returns>
        public int T_Hospital_Add(T_Hospital Hosp)
        {
            string sql = "";
            //判断传递过来的类中是否存在ID值 不存在说明是添加   存在则是修改
            if (Hosp.Hosp_ID==0)
            {
                sql += string.Format("insert into T_Hospital values ('{0}','{1}','{2}','{3}','{4}','{5})'", Hosp.Hosp_Name, Hosp.Hosp_Message, Hosp.Hosp_Image, Hosp.Hosp_Phone, Hosp.Hosp_Address, Hosp.Office_ID);
            }
            else
            {
                sql += string.Format("update T_Hospital set Hosp_Name = '{0}', Hosp_Message = '{1}', Hosp_Image = '{2}', Hosp_Phone = '{3}',Hosp_Address = '{4}', Office_ID = '{5}' where Hosp_ID = '{6}'", Hosp.Hosp_Name, Hosp.Hosp_Message, Hosp.Hosp_Image, Hosp.Hosp_Phone, Hosp.Hosp_Address, Hosp.Office_ID, Hosp.Hosp_ID);
            }
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return result;
        }
        /// <summary>
        /// 科室数据
        /// </summary>
        /// <returns></returns>
        public List<T_Office> T_Office()
        {
            SqlDataAdapter da = new SqlDataAdapter(@"SELECT * FROM T_Office", con);
            DataTable de = new DataTable();
            da.Fill(de);
            var s = JsonConvert.SerializeObject(de);
            var list = JsonConvert.DeserializeObject<List<T_Office>>(s);
            return list;
        }
        /// <summary>
        /// 医院数据、单挑、全部
        /// </summary>
        /// <param name="id">单挑查询的医院ID值</param>
        /// <returns></returns>
        public userinfos<T_Hospital> T_Hospitalfenye(int pageindex, int pagesize,string Hosp_ID,string T_Hospital)
        {
            string sql = "select * from "+ T_Hospital + "";
            SqlCommand cmd = new SqlCommand("Pro_wannengfenye", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageindex", pageindex);
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@order", Hosp_ID);
            cmd.Parameters.AddWithValue("@tablename", T_Hospital);
            cmd.Parameters.AddWithValue("@totalCount", SqlDbType.Int);
            cmd.Parameters["@totalCount"].Direction = ParameterDirection.Output;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable de = new DataTable();
            da.Fill(de);
            var totalcount = (int)cmd.Parameters["@totalCount"].Value;
            var data = JsonConvert.SerializeObject(de);
            var result = JsonConvert.DeserializeObject<List<T_Hospital>>(data);
            userinfos<T_Hospital> list = new userinfos<T_Hospital>();
            list.Itemlist = result;
            list.pageindex = pageindex;
            list.pagesize = pagesize;
            if (totalcount % pagesize == 0)
            {
                list.totalcount = totalcount / pagesize;
            }
            else
            {
                list.totalcount = totalcount / pagesize + 1;
            }
            return list;
        }
        /// <summary>
        /// 医院数据、单挑、全部
        /// </summary>
        /// <param name="id">单挑查询的医院ID值</param>
        /// <returns></returns>
        public T_Doctoruserinfos<T_Doctor> T_Doctorfenye(int pageindex, int pagesize, string Doctor_ID, string T_Doctor)
        {
            string sql = "select * from " + T_Doctor + "";
            SqlCommand cmd = new SqlCommand("Pro_wannengfenye", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageindex", pageindex);
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            cmd.Parameters.AddWithValue("@order", Doctor_ID);
            cmd.Parameters.AddWithValue("@tablename", T_Doctor);
            cmd.Parameters.AddWithValue("@totalCount", SqlDbType.Int);
            cmd.Parameters["@totalCount"].Direction = ParameterDirection.Output;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable de = new DataTable();
            da.Fill(de);
            var totalcount = (int)cmd.Parameters["@totalCount"].Value;
            var data = JsonConvert.SerializeObject(de);
            var result = JsonConvert.DeserializeObject<List<T_Doctor>>(data);
            T_Doctoruserinfos<T_Doctor> list = new T_Doctoruserinfos<T_Doctor>();
            list.Itemlist = result;
            list.pageindex = pageindex;
            list.pagesize = pagesize;
            if (totalcount % pagesize == 0)
            {
                list.totalcount = totalcount / pagesize;
            }
            else
            {
                list.totalcount = totalcount / pagesize + 1;
            }
            return list;
        }
        /// <summary>
        /// 删除医院信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int T_Hospital_Del(int id)
        {
            string sql = "delete T_Hospital where Hosp_ID =" + id;
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return result;
        }
        public List<T_Hospital> T_Hospital(string id)
        {
            SqlDataAdapter da = new SqlDataAdapter("select * from T_Hospital where Hosp_ID="+id+"", con);
            DataTable de = new DataTable();
            da.Fill(de);
            var s = JsonConvert.SerializeObject(de);
            var list = JsonConvert.DeserializeObject<List<T_Hospital>>(s);
            return list;
        }

    }
}
